import time
import io
import pandas as pd

from bqtool.utils.mykit import functime
from bqtool.utils.tools import to_pdtime_str, get_pg, get_mysql, get_db, pd_save


@functime
def write_to_table(df, table_name, db_engine, schema='public', index=False):
    string_data_io = io.StringIO()
    df.to_csv(string_data_io, sep='|', index=index)
    string_data_io.seek(0)
    with db_engine.connect() as connection:
        with connection.connection.cursor() as cursor:
            copy_cmd = f"COPY {schema}.{table_name} FROM STDIN HEADER DELIMITER '|' CSV"
            cursor.copy_expert(copy_cmd, string_data_io)
        connection.connection.commit()


def read():
    # data = pd.read_csv('../test/data1')
    # data.loc[:, 'PASSTIME'] = to_pdtime_str(data['PASSTIME'], tz=False)
    # data = data.set_index('PASSTIME')
    # data.drop(data.columns[0], axis=1, inplace=True)
    # data = data.iloc[0:10000]
    # data = pd.read_sql_query("select * from t_passcar limit 1000000",client2)
    data = pd.read_pickle('datan')
    return data


@functime
def select(client, sql="""select * from t_pass where "PASSTIME" > '2017-06-01 00:42:46' limit 10 """):
    df = pd.read_sql_query(sql, client)
    print(len(df))
    return df

@functime
def insert(df,client,table):
    df.to_sql(table,client,if_exists='append',index=False)

@functime
def insert_oracle(df,client,table):
    pd_save(df,table,client)

def test():
    # df = pd.concat([v for v in pd.read_sql_query('select * from t_passcar',client2,chcunksize=100000)])
    # data.to_sql('t_passcar',client2,if_exists='append',chunksize=10 ** 3,index=False)
    dd = pd.read_sql_query(
        """select * from t_passcar  where PASSTIME > '2017-06-01 18:42:46' and PASSTIME<'2017-06-01 18:52:46'""",
        client2)
    print(len(dd))


if __name__ == '__main__':
    # client = get_pg()
    # client2 = get_mysql('root','ehl1234','10.2.111.57','tsce',port=4000)
    client2 = get_mysql('root', 'ehl1234', '10.2.111.65', 'grafana')
    oracle = get_db('ehl_ew','ehl1234','10.2.111.66')
    df = pd.DataFrame([['a','1']],columns=['id','kkbh'])
    pd_save(df,'t_ew_flow',oracle)
    print('ok')
    # data = read()
    # insert_oracle(data,oracle,'t_passcar')
    # insert(data,client2,'t_passcar')
    # write_to_table(data, 't_a', client2)
    # select(client2,""" select * from t_passcar  where PASSTIME > '2017-06-01 00:42:46' """)
    # select(client)
    # select(oracle,""" select * from t_passcar  where PASSTIME > to_date('2017-06-01 00:42:46','yyyy-MM-dd HH24:mi:ss') """)
